import json
import pymysql
import requests

connection = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='cityData',
                             charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)

headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.3'
}


def totalPopulation():
    url = 'https://data.stats.gov.cn/easyquery.htm?m=QueryData&dbcode=fsnd&rowcode=zb&colcode=sj&wds=%5B%7B%22wdcode%22%3A%22reg%22%2C%22valuecode%22%3A%22440000%22%7D%5D&dfwds=%5B%7B%22wdcode%22%3A%22zb%22%2C%22valuecode%22%3A%22A0301%22%7D%5D&k1=1711638080549&h=1'

    res = requests.get(url, headers=headers, verify=False, timeout=30)
    datas = list(json.loads(res.text)['returndata']['datanodes'])

    # 映射代码和对应的人口数据类型(万人)
    code_mapping = {
        'A030101': "年末常住人口",
        'A030102': "城镇人口",
        'A030103': "乡村人口"
    }
    cursor = connection.cursor()
    data = {category: [] for category in code_mapping.values()}
    for i in datas[1:]:
        if i['data']['data'] == 0 or i['wds'][0]['valuecode'] not in code_mapping.keys():
            continue
        d = {'year': i['wds'][2]['valuecode'], 'value': i['data']['data']}
        data[code_mapping[i['wds'][0]['valuecode']]].append(d)
        sql = ("INSERT INTO `population` VALUES ('{}','{}','{}','{}','{}')"
               .format(i['wds'][2]['valuecode'], i['data']['data'], code_mapping[i['wds'][0]['valuecode']], '广东省',
                       '万人'))
        try:
            cursor.execute(sql)
        except:
            pass
    connection.commit()
    cursor.close()
    print(data)


def birthRate():
    url = "https://data.stats.gov.cn/easyquery.htm?m=QueryData&dbcode=fsnd&rowcode=zb&colcode=sj&wds=%5B%7B%22wdcode%22%3A%22reg%22%2C%22valuecode%22%3A%22440000%22%7D%5D&dfwds=%5B%7B%22wdcode%22%3A%22zb%22%2C%22valuecode%22%3A%22A0302%22%7D%5D&k1=1711673211633&h=1"

    res = requests.get(url, headers=headers, verify=False, timeout=30)
    datas = list(json.loads(res.text)['returndata']['datanodes'])

    # (‰)
    code_mapping = {
        'A030201': "人口出生率",
        'A030202': "人口死亡率",
        'A030203': "人口自然增长率"
    }
    cursor = connection.cursor()
    data = {category: [] for category in code_mapping.values()}
    for i in datas[1:]:
        if i['data']['data'] == 0 or i['wds'][0]['valuecode'] not in code_mapping.keys():
            continue
        d = {'year': i['wds'][2]['valuecode'], 'value': i['data']['data']}
        data[code_mapping[i['wds'][0]['valuecode']]].append(d)
        sql = ("INSERT INTO `population` VALUES ('{}','{}','{}','{}','{}')"
               .format(i['wds'][2]['valuecode'], i['data']['data'], code_mapping[i['wds'][0]['valuecode']], '广东省',
                       '‰'))
        try:
            cursor.execute(sql)
        except:
            pass
    connection.commit()
    cursor.close()
    print(data)


def ageStructure():
    url = "https://data.stats.gov.cn/easyquery.htm?m=QueryData&dbcode=fsnd&rowcode=zb&colcode=sj&wds=%5B%7B%22wdcode%22%3A%22reg%22%2C%22valuecode%22%3A%22440000%22%7D%5D&dfwds=%5B%7B%22wdcode%22%3A%22zb%22%2C%22valuecode%22%3A%22A030804%22%7D%5D&k1=1711674093800&h=1"

    res = requests.get(url, headers=headers, verify=False, timeout=60)
    datas = list(json.loads(res.text)['returndata']['datanodes'])

    # 映射代码和对应的人口数据类型(人)
    code_mapping = {
        'A03080401': "抽查人口数",
        'A03080402': "0-14",
        'A03080403': "15-64",
        'A03080404': "65以上"
    }
    cursor = connection.cursor()
    data = {category: [] for category in code_mapping.values()}

    for i in datas[1:]:
        if i['data']['data'] == 0 or i['wds'][0]['valuecode'] not in code_mapping.keys():
            continue
        d = {'year': i['wds'][2]['valuecode'], 'value': i['data']['data']}
        data[code_mapping[i['wds'][0]['valuecode']]].append(d)
        sql = ("INSERT INTO `population` VALUES ('{}','{}','{}','{}','{}')"
               .format(i['wds'][2]['valuecode'], i['data']['data'], code_mapping[i['wds'][0]['valuecode']], '广东省',
                       '人'))
        try:
            cursor.execute(sql)
        except:
            pass
    connection.commit()
    cursor.close()
    print(data)


def educationalLevel():
    url = "https://data.stats.gov.cn/easyquery.htm?m=QueryData&dbcode=fsnd&rowcode=zb&colcode=sj&wds=%5B%7B%22wdcode%22%3A%22reg%22%2C%22valuecode%22%3A%22440000%22%7D%5D&dfwds=%5B%7B%22wdcode%22%3A%22zb%22%2C%22valuecode%22%3A%22A030806%22%7D%5D&k1=1711699278303&h=1"

    res = requests.get(url, headers=headers, verify=False, timeout=60)
    datas = list(json.loads(res.text)['returndata']['datanodes'])

    # 映射代码和对应的人口数据类型(人)
    code_mapping = {
        'A03080601': "抽查人口数",
        'A03080604': "未上过学人口数",
        'A03080605': "未上过学男性人口数",
        'A03080606': "未上过学女性人口数",
        'A03080607': "小学人口数",
        'A03080608': "小学男性人口数",
        'A03080609': "小学女性人口数",
        'A0308060A': "初中人口数",
        'A0308060B': "初中男性人口数",
        'A0308060C': "初中女性人口数",
        'A0308060D': "高中人口数",
        'A0308060E': "高中男性人口数",
        'A0308060F': "高中女性人口数",
        'A0308060G': "大专及以上人口数",
        'A0308060H': "大专及以上男性人口数",
        'A0308060I': "大专及以上女性人口数"
    }
    data = {category: [] for category in code_mapping.values()}
    cursor = connection.cursor()
    for i in datas[1:]:
        if i['data']['data'] == 0 or i['wds'][0]['valuecode'] not in code_mapping.keys():
            continue
        d = {'year': i['wds'][2]['valuecode'], 'value': i['data']['data']}
        data[code_mapping[i['wds'][0]['valuecode']]].append(d)
        sql = ("INSERT INTO `population` VALUES ('{}','{}','{}','{}','{}')"
               .format(i['wds'][2]['valuecode'], i['data']['data'], code_mapping[i['wds'][0]['valuecode']], '广东省',
                       '人'))
        try:
            cursor.execute(sql)
        except:
            pass
    connection.commit()
    cursor.close()
    print(data)


def householdSizeAndPopulation():
    url = "https://data.stats.gov.cn/easyquery.htm?m=QueryData&dbcode=fsnd&rowcode=zb&colcode=sj&wds=%5B%7B%22wdcode%22%3A%22reg%22%2C%22valuecode%22%3A%22440000%22%7D%5D&dfwds=%5B%7B%22wdcode%22%3A%22zb%22%2C%22valuecode%22%3A%22A030808%22%7D%5D&k1=1711710470253&h=1"

    res = requests.get(url, headers=headers, verify=False, timeout=60)
    datas = list(json.loads(res.text)['returndata']['datanodes'])
    cursor = connection.cursor()
    # 映射代码和对应的家庭户户数(户)
    code_mapping = {
        'A03080801': "抽查家庭户户数",
        'A03080802': "抽查家庭户户数",
        'A03080803': "二人户家庭户户数",
        'A03080804': "三人户家庭户户数",
        'A03080805': "四人户家庭户户数",
        'A03080806': "五人户家庭户户数",
        'A03080807': "六人户家庭户户数",
        'A03080808': "七人户家庭户户数",
        'A03080809': "八人户家庭户户数",
        'A0308080A': "九人户家庭户户数",
        'A0308080B': "十人及以上户家庭户户数"
    }

    data = {category: [] for category in code_mapping.values()}

    for i in datas[1:]:
        if i['data']['data'] == 0 or i['wds'][0]['valuecode'] not in code_mapping.keys():
            continue
        d = {'year': i['wds'][2]['valuecode'], 'value': i['data']['data']}
        data[code_mapping[i['wds'][0]['valuecode']]].append(d)
        sql = ("INSERT INTO `population` VALUES ('{}','{}','{}','{}','{}')"
               .format(i['wds'][2]['valuecode'], i['data']['data'], code_mapping[i['wds'][0]['valuecode']], '广东省',
                       '户'))
        try:
            cursor.execute(sql)
        except:
            pass
    connection.commit()
    cursor.close()
    print(data)


if __name__ == '__main__':
    totalPopulation()
    birthRate()
    ageStructure()
    educationalLevel()
    householdSizeAndPopulation()
